﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using Common.Utilities.OpenXml;
using Workflows.Components.Reports.OpenXmlUtil;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;
using Workbook=Microsoft.Office.Interop.Excel.Workbook;
using Worksheet=Microsoft.Office.Interop.Excel.Worksheet;

namespace Workflows.Components.Reports
{
	/// <summary>
	/// 
	/// </summary>
	public class ExcelFileHelper
	{
		/// <summary>
		/// 
		/// </summary>
		/// <param name="excelFilePath"></param>
		/// <returns></returns>
		public static List<string> GetWorksheetNames(string excelFilePath)
		{
			List<string> sheetNames=new List<string>();
			Application excelApp = new Application();
			try
			{
				if (!File.Exists(excelFilePath))
					throw new Exception("Unable to find excel file");

				
				excelApp.Visible = false;
				excelApp.DisplayAlerts = false;
				object missing = Type.Missing;
				Workbook excelWkbook = excelApp.Workbooks.Open(excelFilePath, missing, missing, missing, missing,
															   missing, missing, missing, missing, missing, missing, missing,
															   missing, missing, missing);
				foreach (Worksheet sheet in excelWkbook.Worksheets)
				{
					sheetNames.Add(sheet.Name);
				}
				excelWkbook.Close(false, null, null);
				Marshal.ReleaseComObject(excelWkbook);
			}
			catch (Exception ex)
			{
				throw new Exception("Unable to read excel file: "+ex.ToString());
			}
			finally
			{
				if (excelApp != null)
				{
					excelApp.Quit();
					Marshal.ReleaseComObject(excelApp);
				}
				GC.Collect();
				GC.WaitForFullGCComplete();
			}

			return sheetNames;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="excelFilePath"></param>
		/// <param name="sheetName"></param>
		/// <returns></returns>
		public static DataTable ReadWorksheetData(string excelFilePath, string sheetName)
		{
			DataTable dt = null;
			Application excelApp = new Application();
			try
			{
				if (!File.Exists(excelFilePath))
					throw new Exception("Unable to find excel file");

				excelApp.Visible = false;
				excelApp.DisplayAlerts = false;
				object missing = Type.Missing;
				Workbook excelWkbook = excelApp.Workbooks.Open(excelFilePath, missing, missing, missing, missing,
															   missing, missing, missing, missing, missing, missing, missing,
															   missing, missing, missing);
				foreach (Worksheet sheet in excelWkbook.Worksheets)
				{
					if (sheet.Name==sheetName)
					{
						dt = new DataTable(sheet.Name);
						Dictionary<string, int> colHeaderIdxMapings = new Dictionary<string, int>();
						int rowNum = 1;
						int maxColIdxThatContainsData = 1;
						int continuousEmptyRowCount = 0;
						while (rowNum <= 25)
						{
							Dictionary<string, int> colHeaderIdxMapingsCopy = new Dictionary<string, int>();
							int colIdx = 1;
							while (colIdx <= 255)
							{
								int continuousEmptyCellCount = 0;
								object cellHeaderValue = GetCell(sheet, colIdx, rowNum).Value2;
								if (cellHeaderValue != null && !string.IsNullOrEmpty(cellHeaderValue.ToString()))
								{
									if (colIdx > maxColIdxThatContainsData)
										maxColIdxThatContainsData = colIdx;
									string colHeader = cellHeaderValue.ToString();
									if (!colHeaderIdxMapingsCopy.ContainsKey(colHeader) && colHeaderIdxMapingsCopy.Count == colIdx - 1)
									{
										colHeaderIdxMapingsCopy.Add(colHeader, colIdx);
										continuousEmptyCellCount = 0;
									}
									else
									{
										continuousEmptyCellCount++;
										if (continuousEmptyCellCount > 5)
											break;
									}
								}
								colIdx++;
							}
							if (colHeaderIdxMapingsCopy.Count > colHeaderIdxMapings.Count)
							{
								colHeaderIdxMapings = colHeaderIdxMapingsCopy;
								continuousEmptyRowCount = 0;
							}
							else 
							{
								continuousEmptyRowCount++;
								if (continuousEmptyRowCount > 5)
									break;
							}
							rowNum++;
						}

						if (maxColIdxThatContainsData == colHeaderIdxMapings.Count)
						{
							string[] colHeaders = new string[colHeaderIdxMapings.Count];
							colHeaderIdxMapings.Keys.CopyTo(colHeaders, 0);
							int rowNumContainingColHeader = colHeaderIdxMapings[colHeaders[0]];
							foreach (string colHeader in colHeaders)
							{
								dt.Columns.Add(colHeader, typeof(string));
							}
							int continuousEmptyRows = 0;
							for (int i = rowNumContainingColHeader + 1; i < 65535; i++)
							{
								DataRow dr = dt.NewRow();
								StringBuilder rowContent = new StringBuilder();
								foreach (string colHeader in colHeaders)
								{
									int colIdx = colHeaderIdxMapings[colHeader];
									object cellValue = GetCell(sheet, colIdx, i).Value2;
									if (cellValue != null)
									{
										dr[colHeader] = cellValue.ToString();
										rowContent.Append(cellValue.ToString());
									}
								}
								if (rowContent.Length > 0)
								{
									dt.Rows.Add(dr);
									continuousEmptyRows = 0;
								}
								else
									continuousEmptyRows++;

								if (continuousEmptyRows >= 5)
									break;
							}
						}
						else
						{
							// auto generate column name, read from row #1 
							for (int i = 1; i <= maxColIdxThatContainsData; i++)
							{
								dt.Columns.Add("Col_" + i.ToString(), typeof(string));
							}
							int continuousEmptyRows = 0;
							for (int i = 1; i < 65535; i++)
							{
								DataRow dr = dt.NewRow();
								StringBuilder rowContent = new StringBuilder();
								for (int k = 1; k <= maxColIdxThatContainsData; k++)
								{
									object cellValue = GetCell(sheet, k, i).Value2;
									if (cellValue != null)
									{
										dr[k] = cellValue.ToString();
										rowContent.Append(cellValue.ToString());
									}
								}
								if (rowContent.Length > 0)
								{
									dt.Rows.Add(dr);
									continuousEmptyRows = 0;
								}
								else
									continuousEmptyRows++;

								if (continuousEmptyRows >= 5)
									break;
							}
						}
					}
				}
				
				excelWkbook.Close(false, null, null);

				Marshal.ReleaseComObject(excelWkbook);
			}
			catch (Exception ex)
			{
				throw new Exception("Unable to read excel data: "+ex.ToString());
			}
			finally
			{
				if (excelApp != null)
				{
					excelApp.Quit();
					Marshal.ReleaseComObject(excelApp);
				}
				GC.Collect();
				GC.WaitForFullGCComplete();
			}

			return dt;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static List<string> ReadColumnHeaders(string excelFilePath, string sheetName)
        {
            List<string> colHeaders = new List<string>();
            Application excelApp = new Application();
            try
            {
                if (!File.Exists(excelFilePath))
                    throw new Exception("Unable to find excel file");

                excelApp.Visible = false;
                excelApp.DisplayAlerts = false;
                object missing = Type.Missing;
                Workbook excelWkbook = excelApp.Workbooks.Open(
                    excelFilePath, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing,
                    missing, missing, missing);
                foreach (Worksheet sheet in excelWkbook.Worksheets)
                {
                    if (sheet.Name == sheetName)
                    {
                        Dictionary<string, int> colHeaderIdxMapings = new Dictionary<string, int>();
                        int rowNum = 1;
                        int maxColIdxThatContainsData = 1;
                        int continuousEmptyRowCount = 0;
                        while (rowNum <= 25)
                        {
                            Dictionary<string, int> colHeaderIdxMapingsCopy = new Dictionary<string, int>();
                            int colIdx = 1;
                            while (colIdx <= 255)
                            {
                                int continuousEmptyCellCount = 0;
                                object cellHeaderValue = GetCell(sheet, colIdx, rowNum).Value2;
                                if (cellHeaderValue != null && !string.IsNullOrEmpty(cellHeaderValue.ToString()))
                                {
                                    if (colIdx > maxColIdxThatContainsData)
                                        maxColIdxThatContainsData = colIdx;
                                    string colHeader = cellHeaderValue.ToString();
                                    if (!colHeaderIdxMapingsCopy.ContainsKey(colHeader) && colHeaderIdxMapingsCopy.Count == colIdx - 1)
                                    {
                                        colHeaderIdxMapingsCopy.Add(colHeader, colIdx);
                                        continuousEmptyCellCount = 0;
                                    }
                                    else
                                    {
                                        continuousEmptyCellCount++;
                                        if (continuousEmptyCellCount > 5)
                                            break;
                                    }
                                }
                                colIdx++;
                            }
                            if (colHeaderIdxMapingsCopy.Count > colHeaderIdxMapings.Count)
                            {
                                colHeaderIdxMapings = colHeaderIdxMapingsCopy;
                                continuousEmptyRowCount = 0;
                            }
                            else 
                            {
                                continuousEmptyRowCount++;
                                if (continuousEmptyRowCount > 5)
                                    break;
                            }
                            rowNum++;
                        }

                        if (colHeaderIdxMapings.Count > 0)
                        {
                            colHeaders.AddRange(colHeaderIdxMapings.Keys);
                        }
                        break;
                    }
                }

                excelWkbook.Close(false, null, null);

                Marshal.ReleaseComObject(excelWkbook);
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to read excel file: "+ex.ToString());
            }
            finally
            {
                if (excelApp != null)
                {
                    excelApp.Quit();
                    Marshal.ReleaseComObject(excelApp);
                }
                GC.Collect();
                GC.WaitForFullGCComplete();
            }
            return colHeaders;
        }

	    /// <summary></summary>
		private static Range GetCell(Worksheet sheet, int colIndex, int rowIndex)
		{
			return (Range)sheet.Cells[rowIndex, colIndex];
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="excelFilePath"></param>
		/// <param name="ds"></param>
		public static void WriteWorksheetData(string excelFilePath, DataSet ds)
		{
			ExcelWriter.CreateWorkbook(excelFilePath, ds);
			/*
			Application excelApp = new Application();
			try
			{
				if (!File.Exists(excelFilePath))
					File.Delete(excelFilePath);

				excelApp.Visible = false;
				excelApp.DisplayAlerts = false;
				object missing = Type.Missing;
                Workbook excelWkbook = excelApp.Workbooks.Add(missing);
				foreach (DataTable dt in ds.Tables)
				{
                    Worksheet sheet = excelWkbook.Sheets.Add(missing, missing, missing, missing) as Worksheet;
					sheet.Name = dt.TableName;
					int colIdx = 0;
					foreach(DataColumn col in dt.Columns)
					{
						colIdx++;
						Range colHeaderCell = WorksheetHelper.GetCell(sheet, colIdx, 1);
						colHeaderCell.Value2 = col.ColumnName;
					}
					for(int i=0;i<dt.Rows.Count;i++)
					{
						int rowIdx = i + 2;
						for(int k=0;k<dt.Columns.Count;k++)
						{
							colIdx = k + 1;
							if (dt.Rows[i][k] != null && dt.Rows[i][k] != DBNull.Value && dt.Rows[i][k].ToString().Length>0)
							{
								Range cell = WorksheetHelper.GetCell(sheet, colIdx, rowIdx);
								cell.Value2 = dt.Rows[i][k];
							}
						}
					}
				}

			    excelWkbook.SaveAs(
			        excelFilePath, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlExclusive, missing, missing,
			        missing, missing, missing);
				excelWkbook.Close(false, null, null);

				Marshal.ReleaseComObject(excelWkbook);
			}
			catch (Exception ex)
			{
				throw new Exception("Unable to read excel data: " + ex.ToString());
			}
			finally
			{
				if (excelApp != null)
				{
					excelApp.Quit();
					Marshal.ReleaseComObject(excelApp);
				}
				GC.Collect();
				GC.WaitForFullGCComplete();
			}
			*/
		}
	}
}
